package net.cathackers.devel.scmp.chat.backends;

import java.util.LinkedList;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;

//import com.mysql.jdbc.ResultSet;
import net.cathackers.devel.scmp.auth.impl.jabber.JabberUserID;
import net.cathackers.devel.scmp.auth.interfaces.UserID;
import net.cathackers.devel.scmp.chat.backends.MySQLPoolBackend;
import net.cathackers.devel.scmp.chat.exceptions.RosterItemAlreadyExistsException;
import net.cathackers.devel.scmp.chat.exceptions.RosterItemNotFoundException;
import net.cathackers.devel.scmp.chat.impl.xmpp.iqcomponent.roster.RosterItem;
import net.cathackers.devel.scmp.chat.interfaces.RosterBackend;
import net.cathackers.devel.scmp.chat.interfaces.RosterSubscriptionManager.Ask;
import net.cathackers.devel.scmp.chat.interfaces.RosterSubscriptionManager.Subscription;
import net.cathackers.devel.scmp.config.impl.RosterDBConfig;
import net.cathackers.devel.scmp.exceptions.BackendException;

public class MySQLRosterBackend extends MySQLPoolBackend implements
	RosterBackend {
    private RosterDBConfig config_;
    private String updateNickNameStmt_;
    private String addNewRosterStmt_;
    private String getRosterStmt_;
    private String deleteRosterItemStmt_;
    private String updateAskStmt_;
    private String updateGroupStmt_;
    private String updateSubscriptionStmt_;
    private String getRosterItemStmt_;
    private String getSubscribersStmt_;
    static final String CONNECTION_POOL_NAME = "scmpRoster";
    static final String CONNECTION_POOL_URI = "jdbc:apache:commons:dbcp:"
	    + CONNECTION_POOL_NAME;

    public MySQLRosterBackend(RosterDBConfig config) {
	super(config, CONNECTION_POOL_NAME, CONNECTION_POOL_URI);
	this.config_ = config;

	updateNickNameStmt_ = "UPDATE " + config_.getTableName() + " SET "
		+ config_.getNickNameFieldName() + " = ?" + " WHERE "
		+ config_.getUserNameFieldName() + " = ? and "
		+ config_.getJIDFieldName() + " = ?";

	updateSubscriptionStmt_ = "UPDATE " + config_.getTableName() + " SET "
		+ config_.getSubscriptionTypeFieldName() + " = ? " + " WHERE "
		+ config_.getUserNameFieldName() + " = ? " + " and "
		+ config_.getJIDFieldName() + " = ?";

	updateGroupStmt_ = "UPDATE " + config_.getTableName() + " SET "
		+ config_.getGroupFieldName() + " = ?" + " WHERE "
		+ config_.getUserNameFieldName() + " = ? and "
		+ config_.getJIDFieldName() + " = ?";

	updateAskStmt_ = "UPDATE " + config_.getTableName() + " SET "
		+ config_.getAskFieldName() + " = ? " + " WHERE "
		+ config_.getUserNameFieldName() + " = ? " + " and "
		+ config_.getJIDFieldName() + " = ?";

	addNewRosterStmt_ = "INSERT INTO " + config_.getTableName()
		+ "(`"+ config_.getUserNameFieldName() +"`, `" + 
		config_.getJIDFieldName() + "`, `" + 
		config_.getAskFieldName() + "`,`" + 
		config_.getSubscriptionTypeFieldName() + "`,`"+ 
		config_.getNickNameFieldName() + "`,`" + 
		config_.getGroupFieldName() + "`) VALUES (?, ?, ?, ?, ?, ?)";

	getRosterStmt_ = "SELECT * FROM " + config_.getTableName() + " WHERE "
		+ config_.getUserNameFieldName() + " LIKE ? ;";

	getRosterItemStmt_ = "SELECT * FROM " + config_.getTableName()
		+ " WHERE " + config_.getUserNameFieldName() + " LIKE ? and "
		+ config_.getJIDFieldName() + " LIKE ? ";

	deleteRosterItemStmt_ = "DELETE FROM " + config_.getTableName()
		+ " WHERE " + config_.getUserNameFieldName() + " LIKE ? and "
		+ config_.getJIDFieldName() + " LIKE ? ;";

	getSubscribersStmt_ = "SELECT * FROM " + config_.getTableName()
	+ " where " + config_.getUserNameFieldName() + "= ? and ("
	+ config_.getSubscriptionTypeFieldName() + " LIKE ? or "
	+ config.getSubscriptionTypeFieldName() + " LIKE ?) ;";

    }

    @Override
    public void addRosterItem(UserID userID, RosterItem item)
	    throws BackendException, RosterItemAlreadyExistsException {

	String jid = item.getJID().getID();
	String sub = item.getSubscription().toString();
	String ask = item.getAsk().getStringValue();
	String group = item.getGroup();
	String nickName = item.getNickname();
	Connection conn = null;
	try {
	    connect();
	    conn = getConnection();
	    PreparedStatement stmt = conn.prepareStatement(addNewRosterStmt_);
	    // TODO change it to Unique Number
	    stmt.setString(1, userID.getUsername());
	    stmt.setString(2, jid);
	    stmt.setString(3, ask);
	    stmt.setString(4, sub);
	    stmt.setString(5, nickName);
	    stmt.setString(6, group);
	    stmt.execute();
	    logger_.info("new item was added in the roster of  "
		    + userID.getUsername());
	    stmt.close();
	} catch (SQLException ex) {

	    logger_.error("Could not Connect To Database : "
		    + config_.getDBName() + " at : " + config_.getHostname()
		    + " to Table: " + config_.getTableName());
	    logger_.error(ex.getMessage());
	    throw new BackendException();
	} finally {
	    try {
		this.killConnection(conn);
	    } catch (BackendException e) {
		logger_.error(e.getMessage());
	    } catch (NullPointerException ex) {
	    }
	}

    }

    @Override
    public LinkedList<RosterItem> getRoster(UserID ownerID)
	    throws RosterItemNotFoundException, BackendException {
	String userName = ownerID.getUsername();
	ResultSet rs = null;
	LinkedList<RosterItem> roster = new LinkedList<RosterItem>();
	Connection conn = null;
	try {
	    connect();
	    conn = getConnection();
	    PreparedStatement stmt = conn.prepareStatement(getRosterStmt_);
	    stmt.setString(1, userName);
	    stmt.execute();
	    rs = stmt.getResultSet();
	    while (rs.next()) {
		RosterItem rosterItem = new RosterItem(rs.getLong(1),
			new JabberUserID(rs.getString(3)), RosterItem
				.getAskType(rs.getString(4)), RosterItem
				.getSubType(rs.getString(5)), rs.getString(7), rs.getString(6));
		roster.add(rosterItem);
	    }
	    stmt.close();
	} catch (SQLException ex) {
	    logger_.error("Could not Connect To Database: "
		    + config_.getDBName() + " at: " + config_.getHostname()
		    + " to Table: " + config_.getTableName());
	    logger_.error(ex.getMessage());
	    throw new BackendException();
	} finally {
	    try {
		this.killConnection(conn);
	    } catch (BackendException e) {
		logger_.error(e.getMessage());
	    } catch (NullPointerException ex) {
	    }

	}
	return roster;
    }

    @Override
    public RosterItem getRosterItem(UserID ownerID, UserID itemID)
	    throws RosterItemNotFoundException, BackendException {
	RosterItem item = null;
	ResultSet rs = null;
	String userName = ownerID.getUsername();
	String jid = itemID.getID();
	Connection conn = null;
	try {
	    logger_.debug("Searching the database1");
	    connect();
	    logger_.debug("Searching the database1.5");
	    conn = getConnection();
	    logger_.debug("Searching the database2");
	    PreparedStatement stmt = conn.prepareStatement(getRosterItemStmt_);
	    stmt.setString(1, userName);
	    stmt.setString(2, jid);
	    logger_.debug("Searching the database");
	    stmt.execute();
	    rs = stmt.getResultSet();
	    if (rs.next()) {
		logger_.debug("this Roster contains this item ");
		item = new RosterItem(rs.getLong(1), new JabberUserID(rs
			.getString(3)), RosterItem.getAskType(rs.getString(4)),
			RosterItem.getSubType(rs.getString(5)), rs.getString(7), rs.getString(6));
	    } else {
		throw new RosterItemNotFoundException(
			"Could not Find RosterItem With JId " + jid
				+ " for user " + userName);
	    }
	    stmt.close();
	} catch (SQLException ex) {
	    logger_.error("Could not Connect to Database :"
		    + config_.getDBName() + " at: " + config_.getHostname()
		    + " to Table : " + config_.getTableName());
	    logger_.error(ex.getMessage());
	    throw new BackendException();
	} finally {
	
	    try {
		this.killConnection(conn);
	    } catch (BackendException e) {
		logger_.error(e.getMessage());
	    } catch (NullPointerException ex) {
	    }
	}
	logger_.debug("returning back the item");
	return item;
    }

    @Override
    public UserID[] getSubscribers(UserID user)
	    throws RosterItemNotFoundException, BackendException {

	String userName = user.getUsername();
	ResultSet rs = null;
	LinkedList<UserID> roster = new LinkedList<UserID>();
	Connection conn = null;
	try {
	    connect();
	    conn = getConnection();
	    PreparedStatement stmt = conn.prepareStatement(getSubscribersStmt_);
	    stmt.setString(1, userName);
	    stmt.setString(2, "from");
	    stmt.setString(3, "both");
	    stmt.execute();
	    rs = stmt.getResultSet();

	    while (rs.next()) {
		UserID userID = new JabberUserID(rs.getString(3));
		logger_.info("get the subscriber  " + userID.getID());
		roster.add(userID);
	    }
	    if (roster.isEmpty()) {
		throw new RosterItemNotFoundException(
			"Could not Find A roster For this User : " + userName);
	    }
	} catch (SQLException ex) {
	    logger_.error("Could not Connect To Database: "
		    + config_.getDBName() + " at: " + config_.getHostname()
		    + " to Table: " + config_.getTableName());
	    logger_.error(ex.getMessage());
	    throw new BackendException();
	} finally {
	    try {
		this.killConnection(conn);
	    } catch (BackendException e) {
		logger_.error(e.getMessage());
	    } catch (NullPointerException ex) {
	    }
	}
	return roster.toArray(new UserID[roster.size()]);
    }

    @Override
    public void removeRosterItem(UserID ownerID, UserID itemID)
	    throws BackendException {

	String userName = ownerID.getUsername();
	String JID = itemID.getID();
	Connection conn = null;
	try {
	    connect();
	    conn = getConnection();
	    PreparedStatement stmt = conn
		    .prepareStatement(deleteRosterItemStmt_);
	    stmt.setString(1, userName);
	    stmt.setString(2, JID);
	    stmt.execute();
	    logger_.debug("this item already removed from the backend");
	    stmt.close();
	} catch (SQLException ex) {
	    logger_.error("Could not Connect to Database : "
		    + config_.getDBName() + " at : " + config_.getHostname()
		    + "to Table: " + config_.getTableName());
	    logger_.error(ex.getMessage());
	} finally {
	    try {
		this.killConnection(conn);
	    } catch (BackendException e) {
		logger_.error(e.getMessage());
	    } catch (NullPointerException ex) {
	    }
	}

    }

    @Override
    public void updateAsk(UserID ownerID, UserID itemID, Ask askType)
	    throws BackendException, RosterItemNotFoundException {
	String userName = ownerID.getUsername();
	String jid = itemID.getID();
	Connection conn = null;
	try {
	    connect();
	    conn = getConnection();
	    PreparedStatement stmt = conn.prepareStatement(updateAskStmt_);
	    stmt.setString(1, askType.getStringValue());
	    stmt.setString(2, userName);
	    stmt.setString(3, jid);
	    stmt.execute();
	    if (stmt.getUpdateCount() < 0) {
		throw new RosterItemNotFoundException(
			"Could not Find Roster Item : " + jid + " For User : "
				+ userName);
	    }
	    stmt.close();
	} catch (SQLException ex) {
	    logger_.error("Could not Establic a Connection to Database : "
		    + config_.getDBName() + " at: " + config_.getHostname()
		    + " to : " + config_.getTableName());
	    logger_.error(ex.getMessage());
	    throw new BackendException();
	} finally {
	    try {
		this.killConnection(conn);
	    } catch (BackendException e) {
		logger_.error(e.getMessage());
	    } catch (NullPointerException ex) {
	    }
	}

    }

    @Override
    public void updateGroup(UserID ownerID, UserID itemID, String group)
	    throws RosterItemNotFoundException, BackendException {

	String userName = ownerID.getUsername();
	String jid = itemID.getID();
	Connection conn = null;
	try {
	    connect();
	    conn = getConnection();
	    PreparedStatement stmt = conn.prepareStatement(updateGroupStmt_);
	    stmt.setString(1, group);
	    stmt.setString(2, userName);
	    stmt.setString(3, jid);
	    stmt.execute();

	    if (stmt.getUpdateCount() < 0) {
		throw new RosterItemNotFoundException(
			"Could not Find Roster Item : " + jid + " For User : "
				+ userName);
	    }
	} catch (SQLException ex) {
	    logger_.error("Could not Establic a Connection to Database : "
		    + config_.getDBName() + " at: " + config_.getHostname()
		    + " to : " + config_.getTableName());
	    logger_.error(ex.getMessage());
	    throw new BackendException();
	} finally {
	    try {
		this.killConnection(conn);
	    } catch (BackendException e) {
		logger_.error(e.getMessage());
	    } catch (NullPointerException ex) {
	    }
	}

    }

    @Override
    public void updateNickname(UserID ownerID, UserID itemID, String nickname)
	    throws RosterItemNotFoundException, BackendException {
	String userName = ownerID.getUsername();
	String jid = itemID.getID();
	Connection conn = null;
	try {
	    connect();
	    conn = getConnection();
	    PreparedStatement stmt = conn.prepareStatement(updateNickNameStmt_);
	    stmt.setString(1, nickname);
	    stmt.setString(2, userName);
	    stmt.setString(3, jid);
	    stmt.execute();
	    if (stmt.getUpdateCount() < 0) {
		throw new RosterItemNotFoundException(
			"Could not Find Roster Item : " + jid + " For User : "
				+ userName);
	    }
	    stmt.close();
	} catch (SQLException ex) {
	    logger_.error("Could not Establic a Connection to Database : "
		    + config_.getDBName() + " at: " + config_.getHostname()
		    + " to : " + config_.getTableName());
	    logger_.error(ex.getMessage());
	    throw new BackendException();
	} finally {
	    try {
		this.killConnection(conn);
	    } catch (BackendException e) {
		logger_.error(e.getMessage());
	    } catch (NullPointerException ex) {
	    }
	}

    }

    @Override
    public void updateSubscription(UserID ownerID, UserID itemID,
	    Subscription sub) throws BackendException,
	    RosterItemNotFoundException {

	String userName = ownerID.getUsername();
	String jid = itemID.getID();
	Connection conn = null;
	try {
	    connect();
	    conn = getConnection();
	    PreparedStatement stmt = conn
		    .prepareStatement(updateSubscriptionStmt_);
	    stmt.setString(1, sub.toString());
	    stmt.setString(2, userName);
	    stmt.setString(3, jid);
	    stmt.execute();
	    if (stmt.getUpdateCount() < 0) {
		throw new RosterItemNotFoundException(
			"Could not Find Roster Item : " + jid + " For User : "
				+ userName);
	    }
	    stmt.close();
	    
	} catch (SQLException ex) {
	    logger_.error("Could not establish a Connection to Database "
		    + config_.getDBName() + "at:" + config_.getHostname()
		    + " at: " + config_.getTableName());
	    logger_.error(ex.getMessage());
	    throw new BackendException();
	} finally {
	    try {
		this.killConnection(conn);
	    } catch (BackendException e) {
		logger_.error(e.getMessage());
	    } catch (NullPointerException ex) {
	    }
	}

    }

    public UserID[] getPublishers(UserID user) throws BackendException, RosterItemNotFoundException {

	String userName = user.getUsername();
	ResultSet rs = null;
	LinkedList<UserID> roster = new LinkedList<UserID>();
	Connection conn = null;
	try {
	    connect();
	    conn = getConnection();
	    PreparedStatement stmt = conn.prepareStatement(getSubscribersStmt_);
	    stmt.setString(1, userName);
	    stmt.setString(2, "to");
	    stmt.setString(3, "both");
	    stmt.execute();
	    rs = stmt.getResultSet();

	    while (rs.next()) {
		UserID userID = new JabberUserID(rs.getString(3));
		logger_.info("get the publisher  " + userID.getID());
		roster.add(userID);
	    }
	    if (roster.isEmpty()) {
		throw new RosterItemNotFoundException(
			"Could not Find A roster For this User : " + userName);
	    }    
	    stmt.close();
	} catch (SQLException ex) {
	    logger_.error("Could not Connect To Database: "
		    + config_.getDBName() + " at: " + config_.getHostname()
		    + " to Table: " + config_.getTableName());
	    logger_.error(ex.getMessage());
	    throw new BackendException();
	} finally {
	    try {
		this.killConnection(conn);
	    } catch (BackendException e) {
		logger_.error(e.getMessage());
	    } catch (NullPointerException ex) {
	    }
	}
	return roster.toArray(new UserID[roster.size()]);
    }

}